{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Database-style Operations on Dataframes\n",
    "\n",
    "## About the data\n",
    "In this notebook, we will using daily weather data that was taken from the [National Centers for Environmental Information (NCEI) API](https://www.ncdc.noaa.gov/cdo-web/webservices/v2). The [0-weather_data_collection.ipynb notebook](https://github.com/stefmolin/Hands-On-Data-Analysis-with-Pandas/blob/master/ch_04/0-weather_data_collection.ipynb) contains the process that was followed to collect the data. \n",
    "\n",
    "*Note: The NCEI is part of the National Oceanic and Atmospheric Administration (NOAA) and, as you can see from the URL for the API, this resource was created when the NCEI was called the NCDC. Should the URL for this resource change in the future, you can search for the NCEI weather API to find the updated one.*\n",
    "\n",
    "## Background on the data\n",
    "\n",
    "Data meanings:\n",
    "- `PRCP`: precipitation in millimeters\n",
    "- `SNOW`: snowfall in millimeters\n",
    "- `SNWD`: snow depth in millimeters\n",
    "- `TMAX`: maximum daily temperature in Celsius\n",
    "- `TMIN`: minimum daily temperature in Celsius\n",
    "- `TOBS`: temperature at time of observation in Celsius\n",
    "- `WESF`: water equivalent of snow in millimeters\n",
    "\n",
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "weather = pd.read_csv('data/nyc_weather_2018.csv')\n",
    "weather.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying DataFrames\n",
    "The `query()` method is an easier way of filtering based on some criteria. For example, we can use it to find all entries where snow was recorded:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>attributes</th>\n",
       "      <th>datatype</th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>GHCND:US1NYWC0019</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>723</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>GHCND:US1NJBG0015</td>\n",
       "      <td>229.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>726</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>GHCND:US1NJBG0017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>730</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>GHCND:US1NJBG0018</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>GHCND:US1NJES0018</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    attributes datatype                 date            station  value\n",
       "124       ,,N,     SNOW  2018-01-01T00:00:00  GHCND:US1NYWC0019   25.0\n",
       "723       ,,N,     SNOW  2018-01-04T00:00:00  GHCND:US1NJBG0015  229.0\n",
       "726       ,,N,     SNOW  2018-01-04T00:00:00  GHCND:US1NJBG0017   10.0\n",
       "730       ,,N,     SNOW  2018-01-04T00:00:00  GHCND:US1NJBG0018   46.0\n",
       "737       ,,N,     SNOW  2018-01-04T00:00:00  GHCND:US1NJES0018   10.0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "snow_data = weather.query('datatype == \"SNOW\" and value > 0')\n",
    "snow_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is equivalent to quering the `data/weather.db` SQLite database for `SELECT * FROM weather WHERE datatype == \"SNOW\" AND value > 0`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/weather.db') as connection:\n",
    "    snow_data_from_db = pd.read_sql(\n",
    "        'SELECT * FROM weather WHERE datatype == \"SNOW\" AND value > 0', \n",
    "        connection\n",
    "    )\n",
    "\n",
    "snow_data.reset_index().drop(columns='index').equals(snow_data_from_db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note this is also equivalent to creating Boolean masks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather[(weather.datatype == 'SNOW') & (weather.value > 0)].equals(snow_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging DataFrames\n",
    "We have data for many different stations each day; however, we don't know what the stations are just their IDs. We can join the data in the `data/weather_stations.csv` file which contains information from the `stations` endpoint of the NCEI API. Consult the `weather_data_collection.ipynb` notebook to see how this was collected. It looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GHCND:US1CTFR0022</td>\n",
       "      <td>STAMFORD 2.6 SSW, CT US</td>\n",
       "      <td>41.0641</td>\n",
       "      <td>-73.5770</td>\n",
       "      <td>36.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GHCND:US1CTFR0039</td>\n",
       "      <td>STAMFORD 4.2 S, CT US</td>\n",
       "      <td>41.0378</td>\n",
       "      <td>-73.5682</td>\n",
       "      <td>6.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GHCND:US1NJBG0001</td>\n",
       "      <td>BERGENFIELD 0.3 SW, NJ US</td>\n",
       "      <td>40.9213</td>\n",
       "      <td>-74.0020</td>\n",
       "      <td>20.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GHCND:US1NJBG0002</td>\n",
       "      <td>SADDLE BROOK TWP 0.6 E, NJ US</td>\n",
       "      <td>40.9027</td>\n",
       "      <td>-74.0834</td>\n",
       "      <td>16.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GHCND:US1NJBG0003</td>\n",
       "      <td>TENAFLY 1.3 W, NJ US</td>\n",
       "      <td>40.9147</td>\n",
       "      <td>-73.9775</td>\n",
       "      <td>21.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  id                           name  latitude  longitude  \\\n",
       "0  GHCND:US1CTFR0022        STAMFORD 2.6 SSW, CT US   41.0641   -73.5770   \n",
       "1  GHCND:US1CTFR0039          STAMFORD 4.2 S, CT US   41.0378   -73.5682   \n",
       "2  GHCND:US1NJBG0001      BERGENFIELD 0.3 SW, NJ US   40.9213   -74.0020   \n",
       "3  GHCND:US1NJBG0002  SADDLE BROOK TWP 0.6 E, NJ US   40.9027   -74.0834   \n",
       "4  GHCND:US1NJBG0003           TENAFLY 1.3 W, NJ US   40.9147   -73.9775   \n",
       "\n",
       "   elevation  \n",
       "0       36.6  \n",
       "1        6.4  \n",
       "2       20.1  \n",
       "3       16.8  \n",
       "4       21.6  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_info = pd.read_csv('data/weather_stations.csv')\n",
    "station_info.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a reminder, the weather data looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>attributes</th>\n",
       "      <th>datatype</th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>GHCND:US1CTFR0039</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>GHCND:US1NJBG0015</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>GHCND:US1NJBG0015</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>GHCND:US1NJBG0017</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>GHCND:US1NJBG0017</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  attributes datatype                 date            station  value\n",
       "0       ,,N,     PRCP  2018-01-01T00:00:00  GHCND:US1CTFR0039    0.0\n",
       "1       ,,N,     PRCP  2018-01-01T00:00:00  GHCND:US1NJBG0015    0.0\n",
       "2       ,,N,     SNOW  2018-01-01T00:00:00  GHCND:US1NJBG0015    0.0\n",
       "3       ,,N,     PRCP  2018-01-01T00:00:00  GHCND:US1NJBG0017    0.0\n",
       "4       ,,N,     SNOW  2018-01-01T00:00:00  GHCND:US1NJBG0017    0.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can join our data by matching up the `station_info.id` column with the `weather.station` column. Before doing that though, let's see how many unique values we have:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                   262\n",
       "unique                  262\n",
       "top       GHCND:US1NJBG0008\n",
       "freq                      1\n",
       "Name: id, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_info.id.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While `station_info` has one row per station, the `weather` dataframe has many entries per station. Notice it also has fewer uniques:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                 80256\n",
       "unique                  109\n",
       "top       GHCND:USW00094789\n",
       "freq                   4270\n",
       "Name: station, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.station.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When working with joins, it is important to keep an eye on the row count. Some join types will lead to data loss:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(262, 80256)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_info.shape[0], weather.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we will be doing this often, it makes more sense to write a function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[262, 80256]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_row_count(*dfs):\n",
    "    return [df.shape[0] for df in dfs]\n",
    "get_row_count(station_info, weather)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `map()` function is more efficient than list comprehensions. We can couple this with `getattr()` to grab any attribute for multiple dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(262, 5), (80256, 5)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_info(attr, *dfs):\n",
    "    return list(map(lambda x: getattr(x, attr), dfs))\n",
    "get_info('shape', station_info, weather)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default `merge()` performs an inner join. We simply specify the columns to use for the join. The left dataframe is the one we call `merge()` on, and the right one is passed in as an argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>attributes</th>\n",
       "      <th>datatype</th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>value</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27422</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-01-23T00:00:00</td>\n",
       "      <td>GHCND:US1NYSF0061</td>\n",
       "      <td>2.3</td>\n",
       "      <td>GHCND:US1NYSF0061</td>\n",
       "      <td>CENTERPORT 0.9 SW, NY US</td>\n",
       "      <td>40.8917</td>\n",
       "      <td>-73.3831</td>\n",
       "      <td>53.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19317</th>\n",
       "      <td>T,,N,</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-08-10T00:00:00</td>\n",
       "      <td>GHCND:US1NJUN0014</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GHCND:US1NJUN0014</td>\n",
       "      <td>WESTFIELD 0.6 NE, NJ US</td>\n",
       "      <td>40.6588</td>\n",
       "      <td>-74.3358</td>\n",
       "      <td>36.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13778</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>WESF</td>\n",
       "      <td>2018-02-18T00:00:00</td>\n",
       "      <td>GHCND:US1NJMS0089</td>\n",
       "      <td>19.6</td>\n",
       "      <td>GHCND:US1NJMS0089</td>\n",
       "      <td>PARSIPPANY TROY HILLS TWP 1.3, NJ US</td>\n",
       "      <td>40.8716</td>\n",
       "      <td>-74.4055</td>\n",
       "      <td>103.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39633</th>\n",
       "      <td>,,7,0700</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-04-06T00:00:00</td>\n",
       "      <td>GHCND:USC00301309</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GHCND:USC00301309</td>\n",
       "      <td>CENTERPORT, NY US</td>\n",
       "      <td>40.8838</td>\n",
       "      <td>-73.3722</td>\n",
       "      <td>9.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51025</th>\n",
       "      <td>,,W,2400</td>\n",
       "      <td>SNWD</td>\n",
       "      <td>2018-12-14T00:00:00</td>\n",
       "      <td>GHCND:USW00014734</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GHCND:USW00014734</td>\n",
       "      <td>NEWARK LIBERTY INTERNATIONAL AIRPORT, NJ US</td>\n",
       "      <td>40.6825</td>\n",
       "      <td>-74.1694</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      attributes datatype                 date            station  value  \\\n",
       "27422       ,,N,     PRCP  2018-01-23T00:00:00  GHCND:US1NYSF0061    2.3   \n",
       "19317      T,,N,     PRCP  2018-08-10T00:00:00  GHCND:US1NJUN0014    0.0   \n",
       "13778       ,,N,     WESF  2018-02-18T00:00:00  GHCND:US1NJMS0089   19.6   \n",
       "39633   ,,7,0700     PRCP  2018-04-06T00:00:00  GHCND:USC00301309    0.0   \n",
       "51025   ,,W,2400     SNWD  2018-12-14T00:00:00  GHCND:USW00014734    0.0   \n",
       "\n",
       "                      id                                         name  \\\n",
       "27422  GHCND:US1NYSF0061                     CENTERPORT 0.9 SW, NY US   \n",
       "19317  GHCND:US1NJUN0014                      WESTFIELD 0.6 NE, NJ US   \n",
       "13778  GHCND:US1NJMS0089         PARSIPPANY TROY HILLS TWP 1.3, NJ US   \n",
       "39633  GHCND:USC00301309                            CENTERPORT, NY US   \n",
       "51025  GHCND:USW00014734  NEWARK LIBERTY INTERNATIONAL AIRPORT, NJ US   \n",
       "\n",
       "       latitude  longitude  elevation  \n",
       "27422   40.8917   -73.3831       53.6  \n",
       "19317   40.6588   -74.3358       36.3  \n",
       "13778   40.8716   -74.4055      103.6  \n",
       "39633   40.8838   -73.3722        9.1  \n",
       "51025   40.6825   -74.1694        2.1  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inner_join = weather.merge(station_info, left_on='station', right_on='id')\n",
    "inner_join.sample(5, random_state=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can remove the duplication of information in the `station` and `id` columns by renaming one of them before the merge and then simply using `on`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>attributes</th>\n",
       "      <th>datatype</th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>value</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27422</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-01-23T00:00:00</td>\n",
       "      <td>GHCND:US1NYSF0061</td>\n",
       "      <td>2.3</td>\n",
       "      <td>CENTERPORT 0.9 SW, NY US</td>\n",
       "      <td>40.8917</td>\n",
       "      <td>-73.3831</td>\n",
       "      <td>53.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19317</th>\n",
       "      <td>T,,N,</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-08-10T00:00:00</td>\n",
       "      <td>GHCND:US1NJUN0014</td>\n",
       "      <td>0.0</td>\n",
       "      <td>WESTFIELD 0.6 NE, NJ US</td>\n",
       "      <td>40.6588</td>\n",
       "      <td>-74.3358</td>\n",
       "      <td>36.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13778</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>WESF</td>\n",
       "      <td>2018-02-18T00:00:00</td>\n",
       "      <td>GHCND:US1NJMS0089</td>\n",
       "      <td>19.6</td>\n",
       "      <td>PARSIPPANY TROY HILLS TWP 1.3, NJ US</td>\n",
       "      <td>40.8716</td>\n",
       "      <td>-74.4055</td>\n",
       "      <td>103.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39633</th>\n",
       "      <td>,,7,0700</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-04-06T00:00:00</td>\n",
       "      <td>GHCND:USC00301309</td>\n",
       "      <td>0.0</td>\n",
       "      <td>CENTERPORT, NY US</td>\n",
       "      <td>40.8838</td>\n",
       "      <td>-73.3722</td>\n",
       "      <td>9.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51025</th>\n",
       "      <td>,,W,2400</td>\n",
       "      <td>SNWD</td>\n",
       "      <td>2018-12-14T00:00:00</td>\n",
       "      <td>GHCND:USW00014734</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NEWARK LIBERTY INTERNATIONAL AIRPORT, NJ US</td>\n",
       "      <td>40.6825</td>\n",
       "      <td>-74.1694</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      attributes datatype                 date            station  value  \\\n",
       "27422       ,,N,     PRCP  2018-01-23T00:00:00  GHCND:US1NYSF0061    2.3   \n",
       "19317      T,,N,     PRCP  2018-08-10T00:00:00  GHCND:US1NJUN0014    0.0   \n",
       "13778       ,,N,     WESF  2018-02-18T00:00:00  GHCND:US1NJMS0089   19.6   \n",
       "39633   ,,7,0700     PRCP  2018-04-06T00:00:00  GHCND:USC00301309    0.0   \n",
       "51025   ,,W,2400     SNWD  2018-12-14T00:00:00  GHCND:USW00014734    0.0   \n",
       "\n",
       "                                              name  latitude  longitude  \\\n",
       "27422                     CENTERPORT 0.9 SW, NY US   40.8917   -73.3831   \n",
       "19317                      WESTFIELD 0.6 NE, NJ US   40.6588   -74.3358   \n",
       "13778         PARSIPPANY TROY HILLS TWP 1.3, NJ US   40.8716   -74.4055   \n",
       "39633                            CENTERPORT, NY US   40.8838   -73.3722   \n",
       "51025  NEWARK LIBERTY INTERNATIONAL AIRPORT, NJ US   40.6825   -74.1694   \n",
       "\n",
       "       elevation  \n",
       "27422       53.6  \n",
       "19317       36.3  \n",
       "13778      103.6  \n",
       "39633        9.1  \n",
       "51025        2.1  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.merge(station_info.rename(dict(id='station'), axis=1), on='station').sample(5, random_state=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are losing stations that don't have weather observations associated with them, if we don't want to lose these rows, we perform a right or left join instead of the inner join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>attributes</th>\n",
       "      <th>datatype</th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>value</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80404</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:USC00309400</td>\n",
       "      <td>WHITE PLAINS MAPLE M, NY US</td>\n",
       "      <td>41.01667</td>\n",
       "      <td>-73.733330</td>\n",
       "      <td>45.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80405</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:USC00309466</td>\n",
       "      <td>WILLETS POINT</td>\n",
       "      <td>40.80000</td>\n",
       "      <td>-73.766667</td>\n",
       "      <td>16.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80406</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:USC00309576</td>\n",
       "      <td>WOODLANDS ARDSLEY, NY US</td>\n",
       "      <td>41.01667</td>\n",
       "      <td>-73.850000</td>\n",
       "      <td>42.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80407</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:USW00014708</td>\n",
       "      <td>HEMPSTEAD MITCHELL FIELD AFB, NY US</td>\n",
       "      <td>40.73333</td>\n",
       "      <td>-73.600000</td>\n",
       "      <td>38.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80408</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:USW00014786</td>\n",
       "      <td>NEW YORK FLOYD BENNETT FIELD, NY US</td>\n",
       "      <td>40.58333</td>\n",
       "      <td>-73.883330</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      attributes datatype date station  value                 id  \\\n",
       "80404        NaN      NaN  NaN     NaN    NaN  GHCND:USC00309400   \n",
       "80405        NaN      NaN  NaN     NaN    NaN  GHCND:USC00309466   \n",
       "80406        NaN      NaN  NaN     NaN    NaN  GHCND:USC00309576   \n",
       "80407        NaN      NaN  NaN     NaN    NaN  GHCND:USW00014708   \n",
       "80408        NaN      NaN  NaN     NaN    NaN  GHCND:USW00014786   \n",
       "\n",
       "                                      name  latitude  longitude  elevation  \n",
       "80404          WHITE PLAINS MAPLE M, NY US  41.01667 -73.733330       45.7  \n",
       "80405                        WILLETS POINT  40.80000 -73.766667       16.8  \n",
       "80406             WOODLANDS ARDSLEY, NY US  41.01667 -73.850000       42.7  \n",
       "80407  HEMPSTEAD MITCHELL FIELD AFB, NY US  40.73333 -73.600000       38.1  \n",
       "80408  NEW YORK FLOYD BENNETT FIELD, NY US  40.58333 -73.883330        4.9  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_join = station_info.merge(weather, left_on='id', right_on='station', how='left')\n",
    "right_join = weather.merge(station_info, left_on='station', right_on='id', how='right')\n",
    "\n",
    "right_join.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The left and right join as we performed above are equivalent because the side that we kept the rows without matches was the same in both cases:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_join.sort_index(axis=1).sort_values(['date', 'station']).reset_index().drop(columns='index').equals(\n",
    "    right_join.sort_index(axis=1).sort_values(['date', 'station']).reset_index().drop(columns='index')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note we have additional rows in the left and right joins because we kept all the stations that didn't have weather observations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(80256, 10), (80409, 10), (80409, 10)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_info('shape', inner_join, left_join, right_join)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we query the station information for stations that have `NY` in their name, believing that to be all the stations that record weather data for NYC and perform an outer join, we can see where the mismatches occur:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>attributes</th>\n",
       "      <th>datatype</th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>value</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17259</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-05-15T00:00:00</td>\n",
       "      <td>GHCND:US1NJPS0022</td>\n",
       "      <td>0.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76178</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>2018-05-19T00:00:00</td>\n",
       "      <td>GHCND:US1NJPS0015</td>\n",
       "      <td>8.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73410</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>MDPR</td>\n",
       "      <td>2018-08-05T00:00:00</td>\n",
       "      <td>GHCND:US1NYNS0018</td>\n",
       "      <td>12.2</td>\n",
       "      <td>GHCND:US1NYNS0018</td>\n",
       "      <td>HICKSVILLE 1.3 ENE, NY US</td>\n",
       "      <td>40.7687</td>\n",
       "      <td>-73.5017</td>\n",
       "      <td>45.7</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74822</th>\n",
       "      <td>,,N,</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>2018-04-02T00:00:00</td>\n",
       "      <td>GHCND:US1NJMS0016</td>\n",
       "      <td>178.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80256</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:US1NJMS0036</td>\n",
       "      <td>PARSIPPANY TROY HILLS TWP 2.1, NJ US</td>\n",
       "      <td>40.8656</td>\n",
       "      <td>-74.3851</td>\n",
       "      <td>64.3</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80257</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:US1NJMS0039</td>\n",
       "      <td>PARSIPPANY TROY HILLS TWP 1.3, NJ US</td>\n",
       "      <td>40.8533</td>\n",
       "      <td>-74.4470</td>\n",
       "      <td>94.2</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      attributes datatype                 date            station  value  \\\n",
       "17259       ,,N,     PRCP  2018-05-15T00:00:00  GHCND:US1NJPS0022    0.3   \n",
       "76178       ,,N,     PRCP  2018-05-19T00:00:00  GHCND:US1NJPS0015    8.1   \n",
       "73410       ,,N,     MDPR  2018-08-05T00:00:00  GHCND:US1NYNS0018   12.2   \n",
       "74822       ,,N,     SNOW  2018-04-02T00:00:00  GHCND:US1NJMS0016  178.0   \n",
       "80256        NaN      NaN                  NaN                NaN    NaN   \n",
       "80257        NaN      NaN                  NaN                NaN    NaN   \n",
       "\n",
       "                      id                                  name  latitude  \\\n",
       "17259                NaN                                   NaN       NaN   \n",
       "76178                NaN                                   NaN       NaN   \n",
       "73410  GHCND:US1NYNS0018             HICKSVILLE 1.3 ENE, NY US   40.7687   \n",
       "74822                NaN                                   NaN       NaN   \n",
       "80256  GHCND:US1NJMS0036  PARSIPPANY TROY HILLS TWP 2.1, NJ US   40.8656   \n",
       "80257  GHCND:US1NJMS0039  PARSIPPANY TROY HILLS TWP 1.3, NJ US   40.8533   \n",
       "\n",
       "       longitude  elevation      _merge  \n",
       "17259        NaN        NaN   left_only  \n",
       "76178        NaN        NaN   left_only  \n",
       "73410   -73.5017       45.7        both  \n",
       "74822        NaN        NaN   left_only  \n",
       "80256   -74.3851       64.3  right_only  \n",
       "80257   -74.4470       94.2  right_only  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outer_join = weather.merge(\n",
    "    station_info[station_info.name.str.contains('NY')], \n",
    "    left_on='station', right_on='id', how='outer', indicator=True\n",
    ")\n",
    "\n",
    "outer_join.sample(4, random_state=0).append(outer_join[outer_join.station.isna()].head(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These joins are equivalent to their SQL counterparts. Below is the inner join. Note that to use `equals()` you will have to do some manipulation of the dataframes to line them up:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/weather.db') as connection:\n",
    "    inner_join_from_db = pd.read_sql(\n",
    "        'SELECT * FROM weather JOIN stations ON weather.station == stations.id', \n",
    "        connection\n",
    "    )\n",
    "\n",
    "inner_join_from_db.shape == inner_join.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Revisiting the dirty data from [chapter 3's 5-handling_data_issues.ipynb notebook](https://github.com/stefmolin/Hands-On-Data-Analysis-with-Pandas/blob/master/ch_03/5-handling_data_issues.ipynb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01T00:00:00</th>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02T00:00:00</th>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03T00:00:00</th>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04T00:00:00</th>\n",
       "      <td>?</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05T00:00:00</th>\n",
       "      <td>?</td>\n",
       "      <td>0.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               station  PRCP   SNOW    TMAX  TMIN  TOBS  WESF  \\\n",
       "date                                                                            \n",
       "2018-01-01T00:00:00                  ?   0.0    0.0  5505.0 -40.0   NaN   NaN   \n",
       "2018-01-02T00:00:00  GHCND:USC00280907   0.0    0.0    -8.3 -16.1 -12.2   NaN   \n",
       "2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0    -4.4 -13.9 -13.3   NaN   \n",
       "2018-01-04T00:00:00                  ?  20.6  229.0  5505.0 -40.0   NaN  19.3   \n",
       "2018-01-05T00:00:00                  ?   0.3    NaN  5505.0 -40.0   NaN   NaN   \n",
       "\n",
       "                    inclement_weather  \n",
       "date                                   \n",
       "2018-01-01T00:00:00               NaN  \n",
       "2018-01-02T00:00:00             False  \n",
       "2018-01-03T00:00:00             False  \n",
       "2018-01-04T00:00:00              True  \n",
       "2018-01-05T00:00:00               NaN  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dirty_data = pd.read_csv(\n",
    "    'data/dirty_data.csv', index_col='date'\n",
    ").drop_duplicates().drop(columns='SNWD')\n",
    "dirty_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to create two dataframes for the join. We will drop some unecessary columns as well for easier viewing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "valid_station = dirty_data.query('station != \"?\"').copy().drop(columns=['WESF', 'station'])\n",
    "station_with_wesf = dirty_data.query('station == \"?\"').copy().drop(columns=['station', 'TOBS', 'TMIN', 'TMAX'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our column for the join is the index in both dataframes, so we must specify `left_index` and `right_index`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP_x</th>\n",
       "      <th>SNOW_x</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather_x</th>\n",
       "      <th>PRCP_y</th>\n",
       "      <th>SNOW_y</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-30T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>-1.7</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>1.5</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-08T00:00:00</th>\n",
       "      <td>48.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>1.1</td>\n",
       "      <td>False</td>\n",
       "      <td>28.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-13T00:00:00</th>\n",
       "      <td>4.1</td>\n",
       "      <td>51.0</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-3.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-21T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>-2.8</td>\n",
       "      <td>0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>6.6</td>\n",
       "      <td>114.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-02T00:00:00</th>\n",
       "      <td>9.1</td>\n",
       "      <td>127.0</td>\n",
       "      <td>12.8</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>True</td>\n",
       "      <td>14.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     PRCP_x  SNOW_x  TMAX  TMIN  TOBS inclement_weather_x  \\\n",
       "date                                                                        \n",
       "2018-01-30T00:00:00     0.0     0.0   6.7  -1.7  -0.6               False   \n",
       "2018-03-08T00:00:00    48.8     NaN   1.1  -0.6   1.1               False   \n",
       "2018-03-13T00:00:00     4.1    51.0   5.6  -3.9   0.0                True   \n",
       "2018-03-21T00:00:00     0.0     0.0   2.8  -2.8   0.6               False   \n",
       "2018-04-02T00:00:00     9.1   127.0  12.8  -1.1  -1.1                True   \n",
       "\n",
       "                     PRCP_y  SNOW_y  WESF inclement_weather_y  \n",
       "date                                                           \n",
       "2018-01-30T00:00:00     1.5    13.0   1.8                True  \n",
       "2018-03-08T00:00:00    28.4     NaN  28.7                 NaN  \n",
       "2018-03-13T00:00:00     3.0    13.0   3.0                True  \n",
       "2018-03-21T00:00:00     6.6   114.0   8.6                True  \n",
       "2018-04-02T00:00:00    14.0   152.0  15.2                True  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "valid_station.merge(\n",
    "    station_with_wesf, left_index=True, right_index=True\n",
    ").query('WESF > 0').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The columns that existed in both dataframes, but didn't form part of the join got suffixes added to their names: `_x` for columns from the left dataframe and `_y` for columns from the right dataframe. We can customize this with the `suffixes` argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>PRCP_?</th>\n",
       "      <th>SNOW_?</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather_?</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-30T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>-1.7</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>1.5</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-08T00:00:00</th>\n",
       "      <td>48.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>1.1</td>\n",
       "      <td>False</td>\n",
       "      <td>28.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-13T00:00:00</th>\n",
       "      <td>4.1</td>\n",
       "      <td>51.0</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-3.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-21T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>-2.8</td>\n",
       "      <td>0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>6.6</td>\n",
       "      <td>114.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-02T00:00:00</th>\n",
       "      <td>9.1</td>\n",
       "      <td>127.0</td>\n",
       "      <td>12.8</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>True</td>\n",
       "      <td>14.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     PRCP   SNOW  TMAX  TMIN  TOBS inclement_weather  PRCP_?  \\\n",
       "date                                                                           \n",
       "2018-01-30T00:00:00   0.0    0.0   6.7  -1.7  -0.6             False     1.5   \n",
       "2018-03-08T00:00:00  48.8    NaN   1.1  -0.6   1.1             False    28.4   \n",
       "2018-03-13T00:00:00   4.1   51.0   5.6  -3.9   0.0              True     3.0   \n",
       "2018-03-21T00:00:00   0.0    0.0   2.8  -2.8   0.6             False     6.6   \n",
       "2018-04-02T00:00:00   9.1  127.0  12.8  -1.1  -1.1              True    14.0   \n",
       "\n",
       "                     SNOW_?  WESF inclement_weather_?  \n",
       "date                                                   \n",
       "2018-01-30T00:00:00    13.0   1.8                True  \n",
       "2018-03-08T00:00:00     NaN  28.7                 NaN  \n",
       "2018-03-13T00:00:00    13.0   3.0                True  \n",
       "2018-03-21T00:00:00   114.0   8.6                True  \n",
       "2018-04-02T00:00:00   152.0  15.2                True  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "valid_station.merge(\n",
    "    station_with_wesf, left_index=True, right_index=True, suffixes=('', '_?')\n",
    ").query('WESF > 0').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we are joining on the index, an easier way is to use the `join()` method instead of `merge()`. Note that the suffix parameter is now `lsuffix` for the left dataframe's suffix and `rsuffix` for the right one's:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>PRCP_?</th>\n",
       "      <th>SNOW_?</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather_?</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-30T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>-1.7</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>1.5</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-08T00:00:00</th>\n",
       "      <td>48.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>1.1</td>\n",
       "      <td>False</td>\n",
       "      <td>28.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-13T00:00:00</th>\n",
       "      <td>4.1</td>\n",
       "      <td>51.0</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-3.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-21T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>-2.8</td>\n",
       "      <td>0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>6.6</td>\n",
       "      <td>114.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-02T00:00:00</th>\n",
       "      <td>9.1</td>\n",
       "      <td>127.0</td>\n",
       "      <td>12.8</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>True</td>\n",
       "      <td>14.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     PRCP   SNOW  TMAX  TMIN  TOBS inclement_weather  PRCP_?  \\\n",
       "date                                                                           \n",
       "2018-01-30T00:00:00   0.0    0.0   6.7  -1.7  -0.6             False     1.5   \n",
       "2018-03-08T00:00:00  48.8    NaN   1.1  -0.6   1.1             False    28.4   \n",
       "2018-03-13T00:00:00   4.1   51.0   5.6  -3.9   0.0              True     3.0   \n",
       "2018-03-21T00:00:00   0.0    0.0   2.8  -2.8   0.6             False     6.6   \n",
       "2018-04-02T00:00:00   9.1  127.0  12.8  -1.1  -1.1              True    14.0   \n",
       "\n",
       "                     SNOW_?  WESF inclement_weather_?  \n",
       "date                                                   \n",
       "2018-01-30T00:00:00    13.0   1.8                True  \n",
       "2018-03-08T00:00:00     NaN  28.7                 NaN  \n",
       "2018-03-13T00:00:00    13.0   3.0                True  \n",
       "2018-03-21T00:00:00   114.0   8.6                True  \n",
       "2018-04-02T00:00:00   152.0  15.2                True  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "valid_station.join(station_with_wesf, rsuffix='_?').query('WESF > 0').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Joins can be very resource-intensive, so it's a good idea to figure out what type of join you need using set operations before trying the join itself. The pandas set operations are performed on the index, so whichever columns we will be joining on will need to be the index. Let's go back to the `weather` and `station_info` dataframes and set the station ID columns as the index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather.set_index('station', inplace=True)\n",
    "station_info.set_index('id', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The intersection will tell us the stations that are present in both dataframes. The result will be the index when performing an inner join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GHCND:US1CTFR0039', 'GHCND:US1NJBG0015', 'GHCND:US1NJBG0015',\n",
       "       'GHCND:US1NJBG0017', 'GHCND:US1NJBG0017', 'GHCND:US1NJBG0017',\n",
       "       'GHCND:US1NJBG0018', 'GHCND:US1NJBG0018', 'GHCND:US1NJBG0018',\n",
       "       'GHCND:US1NJBG0023',\n",
       "       ...\n",
       "       'GHCND:USW00094789', 'GHCND:USW00094789', 'GHCND:USW00094789',\n",
       "       'GHCND:USW00094789', 'GHCND:USW00094789', 'GHCND:USW00094789',\n",
       "       'GHCND:USW00094789', 'GHCND:USW00094789', 'GHCND:USW00094789',\n",
       "       'GHCND:USW00094789'],\n",
       "      dtype='object', length=80256)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.index.intersection(station_info.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The set difference will tell us what we lose from each side. When performing an inner join, we lose nothing from the `weather` dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.index.difference(station_info.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We lose 153 stations from the `station_info` dataframe, however:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GHCND:US1CTFR0022', 'GHCND:US1NJBG0001', 'GHCND:US1NJBG0002',\n",
       "       'GHCND:US1NJBG0005', 'GHCND:US1NJBG0006', 'GHCND:US1NJBG0008',\n",
       "       'GHCND:US1NJBG0011', 'GHCND:US1NJBG0012', 'GHCND:US1NJBG0013',\n",
       "       'GHCND:US1NJBG0020',\n",
       "       ...\n",
       "       'GHCND:USC00308322', 'GHCND:USC00308749', 'GHCND:USC00308946',\n",
       "       'GHCND:USC00309117', 'GHCND:USC00309270', 'GHCND:USC00309400',\n",
       "       'GHCND:USC00309466', 'GHCND:USC00309576', 'GHCND:USW00014708',\n",
       "       'GHCND:USW00014786'],\n",
       "      dtype='object', length=153)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_info.index.difference(weather.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The symmetric difference will tell us what gets lost from both sides. It is the combination of the set difference in both directions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ny_in_name = station_info[station_info.name.str.contains('NY')]\n",
    "\n",
    "ny_in_name.index.difference(weather.index).shape[0]\\\n",
    "+ weather.index.difference(ny_in_name.index).shape[0]\\\n",
    "== weather.index.symmetric_difference(ny_in_name.index).shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The union will show us everything that will be present after a full outer join. Note that since these are sets (which don't allow duplicates by definition), we must pass unique entries for union:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GHCND:US1CTFR0022', 'GHCND:US1CTFR0039', 'GHCND:US1NJBG0001',\n",
       "       'GHCND:US1NJBG0002', 'GHCND:US1NJBG0003', 'GHCND:US1NJBG0005',\n",
       "       'GHCND:US1NJBG0006', 'GHCND:US1NJBG0008', 'GHCND:US1NJBG0010',\n",
       "       'GHCND:US1NJBG0011',\n",
       "       ...\n",
       "       'GHCND:USW00014708', 'GHCND:USW00014732', 'GHCND:USW00014734',\n",
       "       'GHCND:USW00014786', 'GHCND:USW00054743', 'GHCND:USW00054787',\n",
       "       'GHCND:USW00094728', 'GHCND:USW00094741', 'GHCND:USW00094745',\n",
       "       'GHCND:USW00094789'],\n",
       "      dtype='object', length=262)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.index.unique().union(station_info.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the symmetric difference is actually the union of the set differences:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ny_in_name = station_info[station_info.name.str.contains('NY')]\n",
    "\n",
    "ny_in_name.index.difference(weather.index).union(weather.index.difference(ny_in_name.index)).equals(\n",
    "    weather.index.symmetric_difference(ny_in_name.index)\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
